/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.dairy.database;

import com.dairy.structures.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.*;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.DataSources;

/**
 *
 * @author tecton
 */
public class milkmanDatabase implements DatabaseInterface{

    private static Connection connection = null;
    private Statement stmt = null;
    private ResultSet rs = null;
    private DataSource unpooled = null;
    private DataSource pooled = null;

    public milkmanDatabase()
    {
        try
        {
            java.lang.Class.forName("com.mysql.jdbc.Driver");
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        try {
            // Note: your JDBC driver must be loaded [via Class.forName( ... ) or -Djdbc.properties]
            // prior to acquiring your DataSource!

            // Acquire the DataSource... this is the only c3p0 specific code here

            unpooled = DataSources.unpooledDataSource("jdbc:mysql://localhost/dairy?useUnicode=true&characterEncoding=UTF-8",
                    "milkman",
                    "tecton");
            pooled = DataSources.pooledDataSource(unpooled);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public boolean insertAdmin(Admin admin)
    {
        String username = "'" + admin.username + "'";
        String id_company = "" + admin.id_company;
        String password = "'" + admin.password + "'";
        String sql = "INSERT INTO Admin VALUES(" + username + ","
                + id_company + "," + password + ")";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public boolean insertCompany(Company company)
    {
        String id_company = "" + company.id_company;
        String name = "'" + company.name + "'";
        String tel = "'" + company.tel + "'";
        String address = "'" + company.address + "'";
        String sql = "INSERT INTO Company VALUES(" + id_company + ","
                + name + "," + tel + ","+ address + ")";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public boolean insertDairyproduct(Dairyproduct dairyproduct)
    {
        String property = "" + dairyproduct.property;
        String name = "'" + dairyproduct.name + "'";
        String category = "'" + dairyproduct.category + "'";
        String sql = "INSERT INTO Dairyproduct VALUES(" + name + ","
                + category + "," + property + ")";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public boolean insertMilkman(Milkman milkman)
    {
        String job_number = "" + milkman.job_number;
        String id_company = "" + milkman.id_company;
        String password = "'" + milkman.password + "'";
        String IDnumber = "'" + milkman.IDnumber + "'";
        String tel = "'" + milkman.tel + "'";
        String sql = "INSERT INTO Milkman VALUES(" + job_number + ","
                + id_company + "," + password + ","+ IDnumber + "," + tel + ")";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public boolean insertOrders(Orders order)
    {
        String id = "" + order.id;
        String job_number = "" + order.job_number;
        String username = "'" + order.username + "'";
        String name = "'" + order.name + "'";
        int id_company = order.id_company;
        String day_of_week = "" + order.day_of_week;
        String duration = "" + order.duration;
        String number = "" + order.number;
        String sql = "INSERT INTO Orders VALUES(" + id + ","
                + username + "," + name + ","+ id_company + "," + job_number
                + "," + day_of_week + "," + duration + "," + number + ")";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public boolean insertSell(Sell sell)
    {
        String name = "'" + sell.name + "'";
        String id_company = "" + sell.id_company;
        String price = "" + sell.price;
        String sql = "INSERT INTO Sell VALUES(" + name + ","
                + id_company + "," + price + ")";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public boolean insertUser(User user)
    {
        String username = "'" + user.username + "'";
        String password = "'" + user.password + "'";
        String real_name = "'" + user.real_name + "'";
        String IDnumber = "'" + user.IDnumber + "'";
        String address = "'" + user.address + "'";
        String tel = "'" + user.tel + "'";
        String sql = "INSERT INTO User VALUES(" + username + ","
                + password + "," + real_name + ","+ IDnumber + "," + address + "," + tel + ")";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }
    
    @Override
    public void commit() {
        try {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute("COMMIT;");
        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
    }

    @Override
    public boolean close()
    {
        try {
            stmt.close();
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(milkmanDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }

        return true;
    }

    @Override
    public Milkman getMilkman(int id)
    {
        if (id < 0)
            throw new UnsupportedOperationException("Not supported yet.");
//        String sql = "SELECT * FROM Milkman WHERE job_number = " + id;
        String sql = "CALL getMilkman(" + id + ")";
        Milkman result = new Milkman();
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                result.job_number = rs.getInt("job_number");
                result.id_company = rs.getInt("id_company");
                result.password = rs.getString("password");
                result.tel = rs.getString("tel");
                result.IDnumber = rs.getString("IDnumber");
                sql = "SELECT * FROM Company WHERE id_company = " + result.id_company;
                if (stmt.execute(sql))
                {
                    rs = stmt.getResultSet();
                    rs.first();
                    result.company_name = rs.getString("company_name");
                }
            }
            rs.close();
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return result;
    }

    @Override
    public Company getCompany(String username)
    {
        if (username.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
//        String sql = "SELECT * FROM Admin NATURAL JOIN Company WHERE username = '" + username + "'";
        String sql = "CALL getCompany(" + SQLize(username) + ")";
        Company result = new Company();
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                result.name = rs.getString("company_name");
                result.tel = rs.getString("tel");
                result.address = rs.getString("address");
                result.id_company = rs.getInt("id_company");
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return result;
    }
    
    @Override
    public Milkman[] getFreeMilkman(int id_company)
    {
//        String sql = "select * from milkman where job_number in (select job_"
//                + "number from orders where job_number in (select job_number "
//                + "from milkman where id_company = " + id_company + ") group by "
//                + "job_number having count(id) <= (select avg(n) from (select "
//                + "count(id) as n from orders where job_number in (select "
//                + "job_number from milkman where id_company = " + id_company
//                + ") group by job_number) as o)) LIMIT 8";
        String sql = "CALL getFreeMilkman(" + id_company + ");";
        ArrayList<Milkman> milkman = new ArrayList<Milkman>();
        Milkman result;
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while (rs.next())
                {
                    result = new Milkman();
                    result.job_number = rs.getInt("job_number");
                    result.id_company = rs.getInt("id_company");
                    result.tel = rs.getString("tel");
                    milkman.add(result);
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        Milkman[] resultArray = (Milkman[])milkman.toArray(new Milkman[milkman.size()]);
        return resultArray;
    }

    @Override
    public boolean modifyInfo(Milkman milkman)
    {
        String sql = "UPDATE Milkman SET tel = '" + milkman.tel;
        if (milkman.password != null && !milkman.password.equals(""))
            sql += "',password = '" + milkman.password;
        sql += "' WHERE job_number = " + milkman.job_number;
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
            stmt.execute("COMMIT;");
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            return false;
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public ArrayList<Orders> getMilkmanOrders(int job_number)
    {
        if (job_number < 0)
            throw new UnsupportedOperationException("Not supported yet.");
        ArrayList<Orders> order = new ArrayList<Orders>();
        Orders result;
        String sql = "SELECT * FROM (SELECT * FROM Orders WHERE job_number = "
                + job_number + ") AS O NATURAL JOIN User";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Orders();
                    result.id = rs.getInt("id");
                    result.username = rs.getString("real_name");
                    result.name = rs.getString("name");
                    result.job_number = rs.getInt("job_number");
                    result.day_of_week = rs.getInt("day_of_week");
                    result.duration = rs.getShort("duration");
                    result.number = rs.getInt("number");
                    result.address = rs.getString("address");
                    order.add(result);
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return order;
    }

    @Override
    public double getPrice(String name_company, String name_product)
    {
        if (name_company.equals("") && name_product.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
//        String companySql = "(SELECT * FROM Company WHERE company_name = '"
//                + name_company + "') AS C";
//        String sql = "SELECT * FROM Sell NATURAL JOIN " + companySql + "WHERE name='"
//                + name_product + "'";
        String sql = "CALL getPrice(" + SQLize(name_company) + ","
                + SQLize(name_product) + ")";
        double price = 0;
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                price = rs.getDouble("price");
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return price;
    }

    @Override
    public boolean updateProduct(Dairyproduct product)
    {
        if (product == null)
            throw new UnsupportedOperationException("Not supported yet.");
        String sql = "SELECT * FROM Sell WHERE name = '" + product.name + "'";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                if(rs.next())
                {
                    if (product.price <0)
                    {
                        sql = "DELETE FROM Sell" + " WHERE name = '" + product.name + "'";
                        stmt.execute("COMMIT;");
                    }
                    else
                    {
                        sql = "UPDATE Sell SET price = " + product.price + " WHERE"
                                + "name ='" + product.name + "'";
                        stmt.execute(sql);
                        stmt.execute("COMMIT;");
                    }
                }
                else
                {
                    sql = "SELECT * FROM Dairyproduct WHERE name = '" + product.name + "'";
                    if (stmt.execute(sql))
                    {
                        rs = stmt.getResultSet();
                        sql = "SELECT id_company FROM Company WHERE company_name"
                                + "='" + product.company + "'";
                        stmt.execute(sql);
                        ResultSet subRs = stmt.getResultSet();
                        int id = subRs.getInt("id_company");
                        if (rs.next() && product.price > 0)
                        {
                            sql = "INSERT INTO Sell VALUES('" + product.name
                                    + "'," + id + "," + product.price + ")";
                            stmt.execute(sql);
                            stmt.execute("COMMIT;");
                        }
                        else
                        {
                            sql = "INSERT INTO Dairyproduct VALUES('" + product.name
                                    + "','" + product.category + "'," + product.property
                                    + ")";
                            stmt.execute(sql);
                            sql = "INSERT INTO Sell VALUES('" + product.name
                                    + "'," + id + "," + product.price + ")";
                            stmt.execute(sql);
                            stmt.execute("COMMIT;");
                        }
                    }
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            return false;
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }

    @Override
    public boolean dispatch(int id_order, int job_number)
    {
        if (id_order < 0 || job_number < 0)
            throw new UnsupportedOperationException("Not supported yet.");
        String sql = "UPDATE Orders SET job_number = " + job_number
                                + " WHERE id = " + id_order;
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
            stmt.execute("COMMIT;");
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }
    
    @Override
    public Company[] getWholeCompany()
    {
        ArrayList<Company> company = new ArrayList<Company>();
        Company result;
//        String sql = "SELECT id_company, company_name FROM Company;";
//        String sql = "CALL getWholeCompany();";
        String isolation = "set session transaction isolation level read uncommitted;";
        String RR = "set session transaction isolation level Repeatable Read;";
        String sql = "SELECT * FROM companyInfo;";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(isolation);
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Company();
                    result.id_company = rs.getInt("id_company");
                    result.name = rs.getString("company_name");
                    company.add(result);
                }
            }
            stmt.execute(RR);
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        Company[] resultArray = (Company[])company.toArray(new Company[company.size()]);
        return resultArray;
    }

    @Override
    public String[] getWholeCategory()
    {
        ArrayList<String> result = new ArrayList<String>();
//        String sql = "SELECT DISTINCT category FROM DairyProduct";
//        String sql = "CALL getWholeCategory();";
        String isolation = "set session transaction isolation level read uncommitted;";
        String RR = "set session transaction isolation level Repeatable Read;";
        String sql = "SELECT * FROM categoryInfo;";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(isolation);
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result.add(rs.getString("category"));
                }
            }
            stmt.execute(RR);
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        String[] resultArray = (String[])result.toArray(new String[result.size()]);
        return resultArray;
    }

    @Override
    public Dairyproduct[] getDairyProduct(int id_company, String category)
    {
        if (id_company < 0 && (category == null ||category.equals("")))
            throw new UnsupportedOperationException("Not supported yet.");
        ArrayList<Dairyproduct> dairyproduct = new ArrayList<Dairyproduct>();
        Dairyproduct result = new Dairyproduct();
        String sell = "SELECT * FROM company NATURAL JOIN SELL";
        String sql = "";
        if (id_company > -1)
            sell += " WHERE company.id_company = " + id_company;
        sell = "(" + sell + ") as CS";
        sql = "SELECT * FROM DairyProduct JOIN " + sell + " WHERE DairyProduct"
                + ".name = CS.name";
        if (category != null && !category.equals("null"))
            sql += " AND category = '" + category + "';";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Dairyproduct();
                    result.name = rs.getString("name");
                    result.category = rs.getString("category");
                    result.company = rs.getString("company_name");
                    result.price = rs.getDouble("price");
                    result.property = rs.getShort("property");
                    dairyproduct.add(result);
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        Dairyproduct[] resultArray = (Dairyproduct[])dairyproduct.toArray(new Dairyproduct[dairyproduct.size()]);
        return resultArray;
    }

    @Override
    public Dairyproduct[] getDairyProduct(String category)
    {
        if (category.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
        ArrayList<Dairyproduct> dairyproduct = new ArrayList<Dairyproduct>();
        Dairyproduct result = new Dairyproduct();
        String sql = "SELECT * FROM Dairyproduct NATURAL JOIN Sell NATURAL"
                + " JOIN Company";
        if (!category.equals("null"))
            sql += " WHERE category = '" + category + "'";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Dairyproduct();
                    result.name = rs.getString("name");
                    result.category = rs.getString("category");
                    result.company = rs.getString("company_name");
                    result.price = rs.getDouble("price");
                    result.property = rs.getShort("property");
                    dairyproduct.add(result);
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        Dairyproduct[] resultArray = (Dairyproduct[])dairyproduct.toArray(new Dairyproduct[dairyproduct.size()]);
        return resultArray;
    }

    @Override
    public Dairyproduct getDairyProduct(String name_company, String name_product)
    {
        if (name_company.equals("") && name_product.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
        String companySql = "(SELECT * FROM Company WHERE company_name = '"
                + name_company + "') AS C";
        String sql = "SELECT * FROM Dairyproduct NATURAL JOIN " + companySql
                + " NATURAL JOIN Sell"+ " WHERE name='"
                + name_product + "'";
        Dairyproduct product = new Dairyproduct();
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                product.category = rs.getString("category");
                product.company = rs.getString("company_name");
                product.name = rs.getString("name");
                product.property = rs.getShort("property");
                product.price = rs.getDouble("price");
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return product;
    }

    public String SQLize(String content)
    {
        if (content.equals(""))
            return "null";
        else
            return "'" + content + "'";
    }
    
    static void attemptClose(ResultSet o)
    {
	try
	    { if (o != null) o.close();}
	catch (Exception e)
	    { e.printStackTrace();}
    }

    static void attemptClose(Statement o)
    {
	try
	    { if (o != null) o.close();}
	catch (Exception e)
	    { e.printStackTrace();}
    }

    static void attemptClose(Connection o)
    {
	try
	    { if (o != null) o.close();}
	catch (Exception e)
	    { e.printStackTrace();}
    }

    @Override
    public User getUser(String username) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Admin getAdmin(String username) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean modifyInfo(User user) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean modifyInfo(Company company) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ArrayList<Orders> getOrders(String username) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Orders getOrder(String username, String name) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int getOrderMaxID() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Orders[] getFreeOrder() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean updateOrder(Orders order) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean registerUser(User newuser) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}